Exception: Length of LOB data (96550) to be replicated exceeds configured maximum 65536.

We got below exception recently as Lengh of LOB data to be replicated increased the configured limit 65536

Exception Thrown In InboundMessageProcessor ProcessExternalXML Method with [Error]: Internal Xml Parser Failed. Aborting Database Transaction. Current XML Node <FIInvocationSynchronousEvent>, Depth 1.

[InternalXMLParser Error]: Failed to execute business component method. Assembly: FlexNet.SVL.BusinessFacade.Utility, Version=1.0.0.0, Culture=neutral, PublicKeyToken=33f692327842122b, Class: FlexNet.SVL.BusinessFacade.Utility.TableAction, MethodName: TableUpdate, Exception: Length of LOB data (96550) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type.
The statement has been terminated.

Run below command to find the falue

select * from sys.configurations

where name like 'max text repl size%';

GO

You can configure the ‘max text repl size’ to unlimited by using below command

EXEC sp_configure 'max text repl size', -1 ;

RECONFIGURE;

GO

You should be able to see updated value now.

select * from sys.configurations

where name like 'max text repl size%';

GO

I see few users complained that’s this setting lost, you can use ‘OVERRIDE’ option.

EXEC sys.sp_configure N'max text repl size (B)', N'-1'

GO

RECONFIGURE WITH OVERRIDE

GO

Regards
Satishbabu Gunukula
http://sqlserver-expert.com

Database "Not synchronizing / suspect" in Always On High Availability Groups

We recently configured Always On with 2 secondary servers. One secondary is synchronous commit and another with Asynchronous commit.

We are running log backup but noticed that secondary couldn’t redo the log at secondary and kept on piling the log and the drive got full.

Run below command to see what log is not clearing out

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'HRDB';

Name    log_reuse_wait_desc

--------  -------------------------------

HRDB    AVAILABILITY_REPLICA

For short term add space or you might have to remove database from AlwaysOn Group and add it back. 

In the log we see “Could not redo log record” message 

Could not redo log record (40557:30625:14), for transaction ID (0:196794283), on page (1:1033), allocation unit 72058591529205760, database 'HRDB' (database ID 7). Page: LSN = (40557:30379:326), allocation unit = 72058646460891136, type = 10. Log: OpCode = 7, context 10, PrevPageLSN: (40557:30625:12). Restore from a backup of the database, or repair the database.

After troubleshooting it looks like a Bug and need to apply Cumulative patch

https://support.microsoft.com/en-us/help/3173471/fix-could-not-redo-log-record-error-and-replica-is-suspended-in-sql-se

Thanks
Satishbabu Gunukula
http://www.sqlserver-expert.com

How to determine SQLServer Edition

There are many ways you can find the SQLServer edition and version

Please find few methods

1. Connect to SQLServer Instance and run below command
Select @@version

2. Use a SQL Script
Refer link https://gallery.technet.microsoft.com/Determining-which-version-af0f16f6

3. Using Power Shell
Refer link
https://gallery.technet.microsoft.com/Determining-the-version-of-62136c05

4. Open SQL Server Configuration Manager

• Highlight SQLServer Services 
•  Go to Properties à Advanced 
•  Brose to “Stock keeping unit name” and “Version” 

Thanks

Satishbabu Gunukula

java.sql.SQLException: Length of LOB data () to be replicated exceeds configured maximum 65536

We recently come across below database error when working with an application.

java.sql.SQLException: Length of LOB data (65594) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536.

The maximum value for max text repl size defaults to 65536. We get this error when a truncation data size for any of the replicated text column exceeds the limit.

To check the current max text replication size run below command.

USE <DB Name>;
GO
EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size';
GO


Output:-

name                              minimum           maximum    config_value      run_value

max text repl size (B)     -1                      2147483647    65536                   65536

You can set the value to maximum by running below command.

EXEC sp_configure 'show advanced options', 1 ;
RECONFIGURE ;
GO
EXEC sp_configure 'max text repl size', -1 ;
GO
RECONFIGURE;
GO

Note that -1 indicates that there is no limit set for 'max text repl size' other than imposed by the data type.

You can do the same using SSMS

1. Open SSMS and connect to Server/instance
2. Right-click on the server/instance name and choose properties
3. Select “Advanced” options on the properties page.
4. Under  “Miscellaneous” header  you will see the current value of  
   “Max text replication Size”.
5. Change the default value from 65536 to -1 or 2147483647 (depending on the SQLserver) and press OK.

 Regards

Satishbabu Gunukula

http://sqlserver-expert.com

The multi-part identifier "syncobj_" could not be bound

We recently got the below error while executing the a procedure on SQLServer

The multi-part identifier "syncobj_0x3946443335424338.DF_286" could not be bound. (Tested Started By)

Processing...           TAB(13) Complaints 2 - V_TAB_13

The multi-part identifier "syncobj_0x3946443335424338.DF_455" could not be bound. (Test Completed By)

Processing...           TAB(14) Complaints 3 - V_TAB_14

The multi-part identifier "syncobj_0x3946443335424338.DF_280" could not be bound. (Test Submitted By)

Processing...           TAB(15) ASR - V_TAB_15

The multi-part identifier "syncobj_0x3946443335424338.DF_445" could not be bound. (Test Approved By)

The error is confusing becoz the “syncobj_” are related to replication snapshot. Why the procedure calling those objects.

After investigation found that user querying column info from  information_schema.columns in the procedure and there is no filter.

We have added a filter “table_name not like 'syncobj%' " to exclude the “syncobj_” and the procedure is working fine without any issues.

The issue has been resolved.

Thanks,

Satishbabu Gunukula
http://sqlserver-expert.com